﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using popLib;
using PhotoMgr.src.common;
using System.Windows.Forms;
using System.Data;

namespace PhotoMgr.src.database
{
    public partial class DBManager
    {
        public static ReturnSet Search_Image(int page, int itemPerPage, Hashtable searchData)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());
            //var entity = new PhotoMgrEntities(GenConnectionString());
            entity.CommandTimeout = 180;

            IQueryable<View_ImageDetail> result = entity.View_ImageDetail.OrderBy(x => x.ImageID);

            if (searchData.ContainsKey("ImageTitle"))
            {
                string Imagename = searchData["ImageTitle"].ToString();
                result = result.Where(x => x.ImageTitle.Contains(Imagename));
            }

            if (searchData.ContainsKey("ArtistID"))
            {
                int ArtistID = NumberLib.getInt(searchData["ArtistID"]);
                if (ArtistID != 0)
                    result = result.Where(x => x.ArtistID == ArtistID);
            }

            if (searchData.ContainsKey("CategoryID"))
            {
                int CategoryID = NumberLib.getInt(searchData["CategoryID"]);
                if( CategoryID != 0)
                    result = result.Where(x => x.CategoryID == CategoryID);
            }

            //if (searchData.ContainsKey("Fullname"))
            //{
            //    string Fullname = searchData["Fullname"].ToString();
            //    result = result.Where(x => x.Fullname.Contains(Fullname));
            //}

            //if (searchData.ContainsKey("RoleCode"))
            //{
            //    int RoleCode = NumberLib.getInt(searchData["RoleCode"]);
            //    result = result.Where(x => x.RoleCode == RoleCode);
            //}

            var pageData = new PageData();

            if (result.Count() > 0)
            {
                //total pages
                pageData.TotalPages = result.Count() / itemPerPage;
                if (result.Count() % itemPerPage > 0)
                {
                    pageData.TotalPages++;
                }

                //current page
                pageData.CurrentPage = page;
                if (page > pageData.TotalPages)
                {
                    pageData.CurrentPage = pageData.TotalPages;
                }

                //total records
                pageData.TotalRecords = result.Count();

                //get filter record in page
                result = result.Skip((pageData.CurrentPage - 1) * itemPerPage);
                result = result.Take(itemPerPage);
                pageData.DataSource = result.ToList();
            }
            else
            {
                pageData = new PageData(1, 1, result.ToList(), 0);
            }

            return new ReturnSet(true, pageData);
            //return ProcessPageData(page, result, itemPerPage);            
        }

        public static List<Round> SearchRound(int contest_id)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());

            List<Round> result = entity.Rounds.Where(x => x.ContestID == contest_id).ToList<Round>();

            return result;
        }

        public static List<ContestUser> SearchContestUser(int contest_id)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());

            List<ContestUser> result = entity.ContestUsers.Where(x => x.ContestID == contest_id).ToList<ContestUser>();

            return result;
        }

        public static List<View_ContestUserSetting> SearchContestUserSetting(int contest_id)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());

            List<View_ContestUserSetting> result = entity.View_ContestUserSetting.Where(x => x.ContestID == contest_id).ToList<View_ContestUserSetting>();

            return result;
        }

        public static Contest Detail_Contest(int contest_id)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());

            return entity.Contests.Where(x => x.ContestID == contest_id).First();
        }

        public static ReturnSet Detail_ContestTrung(int id)
        {
            using (var entity = new PhotoMgrEntities(GenConnectionString()))
            {
                var result = entity.Contests.Where(x => x.ContestID.Equals(id));
                if (result.Count() > 0)
                {
                    Contest item = result.First();
                    entity.Detach(item);
                    return new ReturnSet(true, item);
                }
                else
                {
                    return new ReturnSet(false, Global.DBErrorType.RecordNotExisted);
                }
            }
        }

        public static Round Detail_Round(int round_id)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());

            return entity.Rounds.Where(x => x.RoundID == round_id).First();
        }

        public static List<database.Image> SearchImage(int round_id)
        {
            var entity = new PhotoMgrEntities(GenConnectionString());

            List<database.Image> result = new List<database.Image>();

            List<RoundImage> imageRList = entity.RoundImages.Where(x => x.RoundID == round_id).ToList<RoundImage>();

            if (imageRList.Count() > 0)
            {
                foreach (var item in imageRList)
                {
                    database.Image myimage = entity.Images.Where(x => x.ImageID == item.ImageID).First();
                    result.Add(myimage);
                }
            }

            return result;
        }

        public static ReturnSet Save_Contest(bool isUpdateMode, Contest updatedItem, Contest originalItem, List<Round> allchitiet, List<ContestUser> userchitietL)
        {
            using (var entity = new PhotoMgrEntities(GenConnectionString()))
            {
                using (var transaction = entity.BeginTransaction())
                {
                    try
                    {
                        //save BH_Ngoai first============================================
                        entity.Contests.Attach(updatedItem);

                        if (isUpdateMode)
                        {
                            entity.ApplyOriginalValues("Contests", originalItem);
                        }
                        else
                        {
                            entity.ObjectStateManager.ChangeObjectState(updatedItem, EntityState.Added);
                        }

                        entity.SaveChanges();


                        //save BH_Ngoai first============================================

                        //remove all item first
                        //IQueryable<Round> chitietvongthi = entity.Rounds.Where(x => x.ContestID == updatedItem.ContestID);
                        //foreach (var item in chitietvongthi)
                        //{
                        //    entity.Rounds.DeleteObject(item);
                        //}
                        //entity.SaveChanges();

                        ////add all item again============================================
                        //foreach (var item in allchitiet)
                        //{
                        //    item.ContestID = updatedItem.ContestID;
                        //    entity.Rounds.AddObject(item);
                        //}
                        //entity.SaveChanges();

                        IQueryable<Round> chitietvongthi = entity.Rounds.Where(x => x.ContestID == updatedItem.ContestID);
                        List<Round> listChiTiet = chitietvongthi.ToList<Round>();

                        foreach (var item in allchitiet)
                        {
                            if (CheckingRoundInSide(item, listChiTiet)== false)
                            {
                                entity.Rounds.AddObject(item);
                            }
                        }

                        entity.SaveChanges();

                        foreach (var item in listChiTiet)
                        {
                            if(CheckingRoundInSide(item, allchitiet) == false)
                            {
                                entity.Rounds.DeleteObject(item);
                            }
                        }

                        entity.SaveChanges();

                        IQueryable<ContestUser> userList = entity.ContestUsers.Where(x => x.ContestID == updatedItem.ContestID);
                        foreach (var item in userList)
                        {
                            entity.ContestUsers.DeleteObject(item);
                        }

                        entity.SaveChanges();

                        foreach (var item in userchitietL)
                        {
                            entity.ContestUsers.AddObject(item);
                        }

                        entity.SaveChanges();

                        //foreach (var item in allchitiet)
                        //{
                        //    item.ContestID = updatedItem.ContestID;
                        //    listChiTiet.Add(item);
                        //}
                        //listChiTiet = listChiTiet.OrderBy(x => x.RoundLevel).ToList<Round>();

                        //foreach (var item in listChiTiet)
                        //{
                        //    if (item.RoundID == 0)
                        //    {
                        //        entity.Rounds.AddObject(item);
                        //    }
                        //}
                        //entity.SaveChanges();

                        //commit here============================================
                        transaction.Commit();

                        return new ReturnSet(true, updatedItem);
                    //}
                    //catch (OptimisticConcurrencyException ocex)
                    //{
                    //    entity.Contests.Detach(updatedItem);
                    //    transaction.Rollback();
                    //    Global.WriteLog(ocex, "Save_KeToan_BienPhis.OptimisticConcurrencyException");
                    //    return new ReturnSet(false, Global.DBErrorType.RecordOutOfDate, ocex.Message);
                    //}
                    //catch (UpdateException uex)
                    //{
                    //    entity.Contests.Detach(updatedItem);
                    //    transaction.Rollback();
                    //    Global.WriteLog(uex, "Save_KeToan_BienPhis.UpdateException");
                    //    Global.WriteLog(uex.InnerException, "Save_KeToan_BienPhis.UpdateException.InnerException");
                    //    return new ReturnSet(false, Global.DBErrorType.AddExistedRecord, uex.Message);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                        entity.Contests.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(ex, "Save_KeToan_BienPhis.Exception");
                        Global.WriteLog(ex.InnerException, "Save_KeToan_BienPhis.Exception.InnerException");
                        return new ReturnSet(false, Global.DBErrorType.UnknownError, ex.Message);
                    }
                }
            }
        }

        private static bool CheckingRoundInSide(Round item, List<Round> listCheck)
        {
            foreach (var itemX in listCheck)
            {
                if (itemX.RoundID == item.RoundID)
                {
                    return true;
                }
            }
            return false;
        }

        private static bool CheckingRoundInSide(List<Round> ListDB, List<Round> ListUD)
        {
            foreach (var item in ListDB)
            {
                foreach (var itemX in ListUD)
                {
                    if (item.RoundID == itemX.RoundID)
                    {
                        return true;
                    }
                }
            }

            return false;
        }

        public static ReturnSet Save_RoundImage(bool isUpdateMode, Round updatedItem, Round originalItem, List<RoundImage> allchitiet)
        {
            using (var entity = new PhotoMgrEntities(GenConnectionString()))
            {
                using (var transaction = entity.BeginTransaction())
                {
                    try
                    {
                        //save BH_Ngoai first============================================
                        entity.Rounds.Attach(updatedItem);

                        if (isUpdateMode)
                        {
                            entity.ApplyOriginalValues("Rounds", originalItem);
                        }
                        else
                        {
                            entity.ObjectStateManager.ChangeObjectState(updatedItem, EntityState.Added);
                        }

                        entity.SaveChanges();


                        //save BH_Ngoai first============================================

                        //remove all item first
                        //IQueryable<Round> chitietvongthi = entity.Rounds.Where(x => x.ContestID == updatedItem.ContestID);
                        //foreach (var item in chitietvongthi)
                        //{
                        //    entity.Rounds.DeleteObject(item);
                        //}
                        //entity.SaveChanges();

                        ////add all item again============================================
                        //foreach (var item in allchitiet)
                        //{
                        //    item.ContestID = updatedItem.ContestID;
                        //    entity.Rounds.AddObject(item);
                        //}
                        //entity.SaveChanges();

                        IQueryable<RoundImage> chitietvongthi = entity.RoundImages.Where(x => x.RoundID == updatedItem.RoundID);
                        List<RoundImage> listChiTiet = chitietvongthi.ToList<RoundImage>();
                        foreach (var item in listChiTiet)
                        {
                            //item.RoundID = updatedItem.RoundID;
                            //listChiTiet.Remove(item);
                            entity.RoundImages.DeleteObject(item);
                        }
                        //listChiTiet = listChiTiet.OrderBy(x => x.RoundImageID).ToList<RoundImage>();

                        foreach (var item in allchitiet)
                        {
                            //if (item.RoundImageID == 0)
                            //{
                                item.Score = 0;
                                item.Status = "A";
                                entity.RoundImages.AddObject(item);
                            //}
                        }
                        entity.SaveChanges();

                        //commit here============================================
                        transaction.Commit();

                        return new ReturnSet(true, updatedItem);
                    }
                    catch (OptimisticConcurrencyException ocex)
                    {
                        entity.Rounds.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(ocex, "Save_Image.OptimisticConcurrencyException");
                        return new ReturnSet(false, Global.DBErrorType.RecordOutOfDate, ocex.Message);
                    }
                    catch (UpdateException uex)
                    {
                        entity.Rounds.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(uex, "Save_Image.UpdateException");
                        Global.WriteLog(uex.InnerException, "Save_KeToan_BienPhis.UpdateException.InnerException");
                        return new ReturnSet(false, Global.DBErrorType.AddExistedRecord, uex.Message);
                    }
                    catch (Exception ex)
                    {
                        entity.Rounds.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(ex, "Save_Image.Exception");
                        Global.WriteLog(ex.InnerException, "Save_KeToan_BienPhis.Exception.InnerException");
                        return new ReturnSet(false, Global.DBErrorType.UnknownError, ex.Message);
                    }
                }
            }
        }

        public static ReturnSet Save_ContestUser(bool isUpdateMode, Contest updatedItem, Contest originalItem, List<ContestUser> allchitiet)
        {
            using (var entity = new PhotoMgrEntities(GenConnectionString()))
            {
                using (var transaction = entity.BeginTransaction())
                {
                    try
                    {
                        //save BH_Ngoai first============================================
                        entity.Contests.Attach(updatedItem);

                        if (isUpdateMode)
                        {
                            entity.ApplyOriginalValues("Contests", originalItem);
                        }
                        else
                        {
                            entity.ObjectStateManager.ChangeObjectState(updatedItem, EntityState.Added);
                        }

                        entity.SaveChanges();


                        //save BH_Ngoai first============================================

                        //remove all item first
                        //IQueryable<Round> chitietvongthi = entity.Rounds.Where(x => x.ContestID == updatedItem.ContestID);
                        //foreach (var item in chitietvongthi)
                        //{
                        //    entity.Rounds.DeleteObject(item);
                        //}
                        //entity.SaveChanges();

                        ////add all item again============================================
                        //foreach (var item in allchitiet)
                        //{
                        //    item.ContestID = updatedItem.ContestID;
                        //    entity.Rounds.AddObject(item);
                        //}
                        //entity.SaveChanges();

                        IQueryable<ContestUser> chitietvongthi = entity.ContestUsers.Where(x => x.ContestID == updatedItem.ContestID);
                        List<ContestUser> listChiTiet = chitietvongthi.ToList<ContestUser>();
                        foreach (var item in listChiTiet)
                        {
                            //item.RoundID = updatedItem.RoundID;
                            //listChiTiet.Remove(item);
                            entity.ContestUsers.DeleteObject(item);
                        }
                        //listChiTiet = listChiTiet.OrderBy(x => x.RoundImageID).ToList<RoundImage>();

                        foreach (var item in allchitiet)
                        {
                            //if (item.RoundImageID == 0)
                            //{
                            item.ContestID = updatedItem.ContestID;
                            entity.ContestUsers.AddObject(item);
                            //}
                        }
                        entity.SaveChanges();

                        //commit here============================================
                        transaction.Commit();

                        return new ReturnSet(true, updatedItem);
                    }
                    catch (OptimisticConcurrencyException ocex)
                    {
                        entity.Contests.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(ocex, "Save_Image.OptimisticConcurrencyException");
                        return new ReturnSet(false, Global.DBErrorType.RecordOutOfDate, ocex.Message);
                    }
                    catch (UpdateException uex)
                    {
                        entity.Contests.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(uex, "Save_Image.UpdateException");
                        Global.WriteLog(uex.InnerException, "Save_KeToan_BienPhis.UpdateException.InnerException");
                        return new ReturnSet(false, Global.DBErrorType.AddExistedRecord, uex.Message);
                    }
                    catch (Exception ex)
                    {
                        entity.Contests.Detach(updatedItem);
                        transaction.Rollback();
                        Global.WriteLog(ex, "Save_Image.Exception");
                        Global.WriteLog(ex.InnerException, "Save_KeToan_BienPhis.Exception.InnerException");
                        return new ReturnSet(false, Global.DBErrorType.UnknownError, ex.Message);
                    }
                }
            }
        }

        public static ReturnSet Contest_GetAvailableRound(int contestID)
        {
            using (var entity = new PhotoMgrEntities(GenConnectionString()))
            {
                var result = entity.Rounds.Where(x => x.ContestID == contestID && x.Status == "A");
                if (result.Count() > 0)
                {
                    Round item = result.First();
                    entity.Detach(item);
                    return new ReturnSet(true, item);
                }
                else
                {
                    return new ReturnSet(false, Global.DBErrorType.RecordNotExisted);
                }
            }
        }
    }
}
